﻿using CommonModel.DB;
using CommonModel.Field;
using CommonModel.Model;
using GrpcPtlSvr;
using Microsoft.EntityFrameworkCore;
using NewLife.Log;
using PtlSave.Util;
using PtlSvr.CfgLoader;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Reflection;

namespace PtlSvr
{
    public class PtlLoader
    {
        private string _dirDll = Environment.CurrentDirectory + @"/ptllibs/";
        public string _dirXml = "D:/temp/schema/";

        private List<Assembly> _asms = new List<Assembly>();
        private List<PtlImpHelper> _ptlImps = new List<PtlImpHelper>();

        public List<PtlLib> ptlLibs = new List<PtlLib>();

        public string connectionString = "";
        public string version = "";

        public bool mysqlConn = false;

        public PtlLoader(string connectionString, string version, string dirDll = "", string dirXml = "")
        {
            this.connectionString = connectionString;
            this.version = version;
            if (dirDll.Length > 0)
            {
                _dirDll = Environment.CurrentDirectory + @dirDll;
            }

            _dirXml = dirXml;
            loadParam();
            loadlibs();
        }

        private void loadParam()
        {
            DbContextOptions<MySQLContext> options = new DbContextOptions<MySQLContext>();
            MySQLContext mySQLContext = new MySQLContext(options, connectionString, version);
            try
            {
                mySQLContext.Database.GetDbConnection().Open();
                mysqlConn = true;

                //从xml文件读取并保存到数据库，会破坏数据库save信息
                //传入该参数时已作判断，无需加载时该参数为空
                if (!"".Equals(_dirXml))
                { 
                    saveXmlToDb(mySQLContext);
                }

                //从数据库读取原xml数据
                SchemaServer.initSchemaMapFromDb(mySQLContext);
            }
            catch (Exception e)
            {
                mysqlConn = false;
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " " + e.Message);
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " MySQL数据库连接失败，无法读取配置文件信息！");
            }
        }

        /*
         * 从xml文件读取并保存到数据库，会破坏数据库save信息
         */
        private void saveXmlToDb(MySQLContext mySQLContext)
        {
            /**********临时代码，把xml的数据写入数据库*************************************/
            //读取xml暂时保留
            loadXmls();

            //执行前提是file_param_main表数据正常
            List<string> sqls = new List<string>();
            string tableNameSource = "file_param_detail";   //真实数据表
            string tableName = "file_param_detail_tmp"; //临时表
            string tableNameTmp = "tmpTb_" + new Random().Next(100, 200);
            string columns = "layer,file_name,parent_id,child_num,name,parent_idx, idx,type,size,device_type,loop_val,code,isalarm,alarmvalue,alarmname, memo";
            string sqlHead = "insert into " + tableName + "(" + columns + ")";
            string file_name_Pre = "";
            DbCommand dbCommand = mySQLContext.Database.GetDbConnection().CreateCommand();
            foreach (var key in SchemaServer.SCHAMA_DICT.Keys)
            {
                string values = " ";
                sqls.Clear();
                var doc = SchemaServer.SCHAMA_DICT[key];
                if (doc.getFile_name().Equals(file_name_Pre))
                {
                    continue;
                }

                //执行前判断file_param_main是否存在该文件数据
                dbCommand.CommandText = "SELECT count(1) cnt FROM file_param_main WHERE file_name = '" + doc.getFile_name() + "'";
                DbDataReader dataReader = dbCommand.ExecuteReader();
                int cnt = 0;
                while (dataReader.Read())
                {
                    cnt = dataReader.GetInt32(0);
                }
                dataReader.Close();

                //不存在时，插入数据
                if (cnt == 0)
                {
                    string sql = "insert into file_param_main(file_name, schema_name, msg_type, xml_type, memo) " + 
                        "values('" + doc.getFile_name() + "','" + doc.getName() + "', '" + doc.getMsgType() + "', '" + doc.getXmlType() + "','后期新增')";
                    sqls.Add(sql);
                }

                //循环得到插入语句
                file_name_Pre = doc.getFile_name();
                Dictionary<int, Object> dynamicParaDictionary = doc.getFieldDictionary();
                for (int i = 0; i < dynamicParaDictionary.Count; i++)
                {
                    Object schObj = dynamicParaDictionary[i];
                    values = values + (i > 0 ? "," : "") + GetValues(schObj, doc.getFile_name(), "-1", 0);
                }
                string sqlIn = sqlHead + " values" + values;
                sqls.Add("TRUNCATE TABLE " + tableName);
                sqls.Add(sqlIn);
                //一堆update语句
                sqls.Add("UPDATE " + tableName + " a SET main_id = (SELECT id FROM file_param_main b WHERE a.file_name = b.file_name)");
                //sqls.Add("DROP TABLE " + tableNameTmp);
                sqls.Add("CREATE TABLE " + tableNameTmp + " AS SELECT id, main_id, idx, parent_idx FROM " + tableName + " WHERE child_num > 0");
                sqls.Add("UPDATE " + tableName + " a SET a.parent_id = (SELECT b.id FROM " + tableNameTmp + " b WHERE a.main_id = b.main_id AND a.parent_idx = b.idx) WHERE a.parent_id = 0");
                sqls.Add("DROP TABLE " + tableNameTmp);
                sqls.Add("CREATE TABLE " + tableNameTmp + " AS SELECT main_id, MIN(id)-1 MID FROM " + tableName + " GROUP BY main_id");
                sqls.Add("UPDATE " + tableName + " a SET sort = (SELECT a.id - b.mid FROM " + tableNameTmp + " b WHERE a.main_id = b.main_id)");
                sqls.Add("DROP TABLE " + tableNameTmp);

                sqls.Add("CREATE TABLE " + tableNameTmp + " AS SELECT id, main_id, idx, parent_idx, child_num, layer FROM " + tableName);
                //sqls.Add("UPDATE " + tableName + " a SET a.parent_id = IFNULL((SELECT b.id FROM " + tableNameTmp + " b WHERE a.layer-1 = b.layer AND a.main_id = b.main_id AND a.parent_idx = b.idx AND b.id < a.id AND b.id >= a.id - b.child_num ), -1)");
                //sqls.Add("UPDATE " + tableName + " a SET a.parent_id = IFNULL((SELECT b.id FROM " + tableNameTmp + " b WHERE a.layer-1 = b.layer AND a.main_id = b.main_id AND a.parent_idx = b.idx AND b.id < a.id ), -1)");
                sqls.Add("UPDATE " + tableName + " a SET a.parent_id = IFNULL((SELECT MAX(b.id) FROM " + tableNameTmp + " b WHERE a.layer-1 = b.layer AND a.main_id = b.main_id AND a.parent_idx = b.idx AND b.id < a.id ), -1)");
                sqls.Add("DROP TABLE " + tableNameTmp);
                sqls.Add("UPDATE " + tableName + " SET loop_new = 1 WHERE (loop_val REGEXP '[A-Za-z]')=1");
                sqls.Add("UPDATE " + tableName + " SET issave = 0 WHERE name in('devid', 'functioncode', 'datalength', 'LLL', 'HHH')");

                //把把xml的数据写入数据库
                //必须全部执行通过
                bool rs = true;
                foreach (string sql in sqls)
                {
                    dbCommand.CommandText = sql;
                    try
                    {
                        dbCommand.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        XTrace.WriteLine("sql Error" + e.Message + ":" + sql);
                        rs = false;

                        //确保临时表删除掉
                        try
                        {
                            dbCommand.CommandText = "DROP TABLE " + tableNameTmp;
                            dbCommand.ExecuteNonQuery();
                        }
                        catch { }
                        break;
                    }
                }

                //执行不通过，进行下一个文件
                if (!rs)
                {
                    continue;
                }

                //获取最大ID
                dbCommand.CommandText = "SELECT MAX(id) maxid FROM " + tableNameSource;
                dataReader = dbCommand.ExecuteReader();
                int maxId = 1;
                while (dataReader.Read())
                {
                    maxId = dataReader.GetInt32(0);
                }
                dataReader.Close();

                //确保数据插入成功
                dbCommand.CommandText = "SELECT COUNT(1) cnt FROM " + tableName;
                dataReader = dbCommand.ExecuteReader();
                int countIns = 0;
                while (dataReader.Read())
                {
                    countIns = dataReader.GetInt32(0);
                }
                dataReader.Close();

                if (countIns > 0)
                {
                    try
                    {
                        dbCommand.CommandText = "update " + tableName + " set id = id + " + maxId;
                        dbCommand.ExecuteNonQuery();

                        dbCommand.CommandText = "update " + tableName + " set parent_id = parent_id + " + maxId + " where parent_id > 0";
                        dbCommand.ExecuteNonQuery();

                        dbCommand.CommandText = "delete from " + tableNameSource + " where main_id = (SELECT DISTINCT main_id FROM " + tableName + ")";
                        dbCommand.ExecuteNonQuery();

                        dbCommand.CommandText = "insert into " + tableNameSource + " select * from " + tableName;
                        dbCommand.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        XTrace.WriteLine("sql Error" + e.Message + ":" + dbCommand.CommandText);
                    }
                }
            }
            /**********临时代码，把xml的数据写入数据库*************************************/
        }

        private string GetValues(Object schObj, string fileName, string parentIdx, int layer)
        {
            if (schObj is FieldSchema)
            {
                FieldSchema fieldSchema = (FieldSchema)schObj;
                return "(" + layer + ",'" + fileName + "',-1, -1, '" + fieldSchema.getName() + "'," + parentIdx + "," + fieldSchema.getIndex() + ",'" + fieldSchema.getType() + "'," + fieldSchema.getSize() + 
                    ",'" + fieldSchema.getDeviceType() + "', '" + fieldSchema.getLoop() + "','" + fieldSchema.getCode() + "'," + (fieldSchema.isAlarm() ? 1 : 0).ToString() + 
                    ",'" + fieldSchema.getAlarmValue() + "','" + fieldSchema.getAlarmName() + "','" + fieldSchema.getMemo() + "')";
            }
            else
            {
                SchemaDictionary schDictionary = (SchemaDictionary)schObj;
                Dictionary<int, Object> dynamicParaDictionaryIn = schDictionary.getFieldDictionary();
                string value = "(" + layer + ",'" + fileName + "',-1, " + dynamicParaDictionaryIn.Count + ", '" + schDictionary.getName() + "', " + parentIdx + "," + schDictionary.getIndex() + ",'" + 
                    schDictionary.getType() + "'," + schDictionary.getSize() + ",'" + schDictionary.getDeviceType() + "', '" + schDictionary.getLoop() + "','" + 
                    schDictionary.getCode() + "'," + (schDictionary.isAlarm() ? 1 : 0).ToString() + ",'" + schDictionary.getAlarmValue() + "','" + schDictionary.getAlarmName() + 
                    "','" + schDictionary.getMemo() + "')";

                int layerNext = layer + 1;
                for (int j = 0; j < dynamicParaDictionaryIn.Count; j++)
                {
                    value = value + "," + GetValues(dynamicParaDictionaryIn[j], fileName, schDictionary.getIndex().ToString(), layerNext);
                }
                return value;
            }
        }

        private void loadXmls()
        {
            //加载所有配置xml
            SchemaServer.initSchemaMap(_dirXml);
        }

        /*
         * 加载配置文件加下所有库文件
         */
        private void loadlibs()
        {
            XTrace.WriteLine("动态库文件路径：" + _dirDll);
            if (Directory.Exists(_dirDll))
            {//判断库文件是否存在
                DirectoryInfo TheFolder = new DirectoryInfo(_dirDll);
                FileInfo[] files = TheFolder.GetFiles();
                XTrace.WriteLine("动态库文件：" + files.Length);
                foreach (FileInfo NextFile in files)
                {
                    //配置文件有配置，才加载
                    byte[] assemblyBuf = File.ReadAllBytes(NextFile.FullName);
                    Assembly assembly = Assembly.Load(assemblyBuf);
                    _asms.Add(assembly);
                    PtlImpHelper ptlhlp = new PtlImpHelper();
                    if (true == ptlhlp.GetMethod(assembly))
                    {
                        XTrace.WriteLine("Load Ptl:" + NextFile.FullName);

                        //加载时设置参数
                        ptlhlp.setCfg(SchemaServer.SCHAMA_DICT);
                        ptlLibs.Add(new PtlLib { Filename = NextFile.Name, Ver = ptlhlp .getver()});
                        _ptlImps.Add(ptlhlp);
                    }
                }
            }
            else
            {
                XTrace.WriteLine("路径" + _dirDll + "不存在，无法加载动态库文件");
            }
        }

        /*
         * 匹配
         */
        public PtlImpHelper matching(byte[] data)
        {
            foreach (PtlImpHelper ptlimp in _ptlImps)
            {
                if (true == ptlimp.matching(data))
                { 
                    return ptlimp;
                }
            }
            return null;
        }
    }
}
